import pymysql


class MySQLHelper:
    def __init__(self, host, user, password, db, port=3306, charset='utf8'):
        self.host = host
        self.user = user
        self.password = password
        self.db = db
        self.port = port
        self.charset = charset
        self.connection = None
        self.cursor = None

    def connect(self):
        self.connection = pymysql.connect(host=self.host, user=self.user, password=self.password, db=self.db,
                                          port=self.port, charset=self.charset)
        self.cursor = self.connection.cursor(pymysql.cursors.DictCursor)

    def execute_query(self, query, params=()):
        if self.connection is None:
            self.connect()
        self.cursor.execute(query, params)
        self.connection.commit()

    #批量新增
    def executemany_query(self, query, params=()):
        if self.connection is None:
            self.connect()
        self.cursor.executemany(query, params)
        self.connection.commit()
        return self.cursor.lastrowid

    def fetch_all(self, query, params=()):
        if self.connection is None:
            self.connect()
        self.execute_query(query, params)
        return self.cursor.fetchall()

    def close(self):
        if self.connection:
            self.cursor.close()
            self.connection.close()

def host():
    return MySQLHelper(host='39.98.204.16', user='python', password='python', db='python')

def select(sql):
    mysql_helper = host()
    mysql_helper.connect()
    results = mysql_helper.fetch_all(sql)
    mysql_helper.close()
    return results

def insrt(sql):
    mysql_helper = host()
    mysql_helper.connect()
    mysql_helper.execute_query(sql)
    mysql_helper.close()

def insrtall(sql,data):
    mysql_helper = host()
    mysql_helper.connect()
    ids = mysql_helper.executemany_query(sql, data)
    mysql_helper.close()
    return ids



# if __name__ == '__main__':
#     mysql_helper = MySQLHelper(host='39.98.204.16', user='go', password='go', db='go')
#     mysql_helper.connect()
#     mysql_helper.execute_query("INSERT INTO mytable (column1, column2) VALUES (%s, %s)", ('value1', 'value2'))
#     results = mysql_helper.fetch_all("SELECT * FROM mytable")
#     for row in results:
#         print(row)
#     mysql_helper.close()